package com.oa;

import com.oa.mapper.TestExcelMapper;
import com.oa.pojo.TestExcel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import org.apache.poi.ss.usermodel.*;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.File;
import java.io.FileInputStream;
import java.util.*;

@SpringBootTest(classes = OAStarter.class)
public class PoiTest3 {

    @Autowired
    private TestExcelMapper testExcelMapper;

    /**
     * 测试导入Excel文件
     *
     * @throws Exception 异常
     */
    @org.junit.Test
    public void test() throws Exception {
        FileInputStream inputStream = new FileInputStream(new File("D:/\\test.xlsx"));
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = sheet.iterator();
        //数据总数
        int sum = 0;
        //数据错误数
        int errorCount = 0;
        //数据成功处理数
        int successCount = 0;
        List<String> errorRow = new ArrayList<>();
        List<TestExcel> list = new ArrayList<>();
        while (iterator.hasNext()) {
            Row row = iterator.next();
            if (isRowEmpty(row)) {
                continue;
            }
            sum++;
            int rowNum = row.getRowNum();
            //不对标题栏进行数据解析
            if (rowNum == 0) {
                continue;
            }
            TestExcel excel = getCurrentRowData(row, workbook);
            list.add(excel);
            if (excel == null) {
                errorCount++;
                errorRow.add("第" + (rowNum + 1) + "行数据有错");
                continue;
            }
        }
    }

    /**
     * 判断当前行是否符合条件
     *
     * @param row 行
     * @return {@link Boolean}
     */
    public Boolean isRowEmpty(Row row) {
        for (int i = 0; i < 5; i++) {
            Cell cell = row.getCell(i);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                return false;
            }
        }
        return true;
    }

    public TestExcel getCurrentRowData(Row row, Workbook workbook) {
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
        String id = row.getCell(0).getStringCellValue();

        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
        String name = row.getCell(1).getStringCellValue();

        row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
        String codeNum = row.getCell(2).getStringCellValue();

        row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
        String areaName = row.getCell(3).getStringCellValue();

        String areaId = null;
        int type = row.getCell(4).getCellType();
        if (type == Cell.CELL_TYPE_FORMULA){
            areaId = evaluator.evaluate(row.getCell(4)).getStringValue();
        } else {
            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
            areaId = row.getCell(4).getStringCellValue();
        }
        return new TestExcel(id, name, codeNum, areaName, areaId);
    }

    @Test
    public void test1() {
        List<TestExcel> testExcels = testExcelMapper.selectList(null);
        System.out.println(testExcels);
    }
}

@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
class Excel {
    String id;
    String name;
    String codeNum;
    String areaName;
    String areaId;
}